﻿using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.MoveHouseConfig.MoveHouseSpecificationServiceConfigOverWrite;
using BCEntity.TMS.MoveHouseConfig.MoveHouseConfig;

namespace BCData.TMS.MoveHouseConfig.MoveHouseSpecificationServiceConfigOverWrite
{
    public class MoveHouseSpecificationServiceConfigOverWriteData : IMoveHouseSpecificationServiceConfigOverWriteData
    {
        private readonly IDatabaseContext databaseContext;
        private readonly string QUERY = @"SELECT
		                                A.MoveHouseSpecificationServiceConfigOverWriteId,
		                                A.MoveHouseSpecificationServiceConfigId,
		                                A.MoveHouseConfigId,
		                                A.MoveHouseSpecificationConfigId,
		                                A.SpecificationName,
		                                A.SpecificationServiceConfigId,
		                                A.SpecificationServiceName,
		                                A.ProvinceCode,
		                                A.CityCode,
		                                A.CountyCode,
		                                A.AgentRecordId,
		                                A.Price,
		                                A.Remark,
		                                A.Enabled,
		                                A.CreateAdminId,
		                                A.CreateAdminName,
		                                A.CreateTime,
                                        A.UpdateTime,
                                        A.EditAdminId,
                                        A.EditAdminName
                                    FROM
                                        MoveHouseSpecificationServiceConfigOverWrite A ";



        #region 构造函数
        public MoveHouseSpecificationServiceConfigOverWriteData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新搬家规格服务配置重写纪录
        /// </summary>
        /// <param name="moveHouseSpecificationServiceConfigOverWriteEntity"></param>
        /// <returns></returns>
        public async Task<MoveHouseSpecificationServiceConfigOverWriteEntity> InsertAsync(MoveHouseSpecificationServiceConfigOverWriteEntity entity)
        {
            return await this.databaseContext.Master.InsertAsync(entity, databaseContext.Transaction);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改搬家规格服务配置重写记录
        /// </summary>
        /// <param name="moveHouseSpecificationServiceConfigOverWriteEntity"></param>
        /// <returns></returns>
        public async Task<MoveHouseSpecificationServiceConfigOverWriteEntity> UpdateAsync(MoveHouseSpecificationServiceConfigOverWriteEntity entity)
        {
            return await this.databaseContext.Master.UpdateAsync(entity, databaseContext.Transaction);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改搬家规格服务配置重写记录
        /// </summary>
        /// <param name="moveHouseSpecificationServiceConfigOverWriteId"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long moveHouseSpecificationServiceConfigOverWriteId)
        {
            var sql = @" DELETE FROM MoveHouseSpecificationServiceConfigOverWrite WHERE MoveHouseSpecificationServiceConfigOverWriteId=?MoveHouseSpecificationServiceConfigOverWriteId ";
            return await this.databaseContext.Master.ExecuteAsync(sql, new { moveHouseSpecificationServiceConfigOverWriteId }, databaseContext.Transaction);
        }
        #endregion 

        #region GetById
        /// <summary>
        /// 获取搬家规格服务配置重写记录
        /// </summary>
        /// <param name="moveHouseSpecificationServiceConfigOverWriteId"></param>
        /// <returns></returns>
        public async Task<MoveHouseSpecificationServiceConfigOverWriteEntity> GetEntityAsync(long moveHouseSpecificationServiceConfigOverWriteId)
        {
            string sql = QUERY + " WHERE A.MoveHouseSpecificationServiceConfigOverWriteId = ?moveHouseSpecificationServiceConfigOverWriteId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MoveHouseSpecificationServiceConfigOverWriteId", moveHouseSpecificationServiceConfigOverWriteId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<MoveHouseSpecificationServiceConfigOverWriteEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询搬家规格服务配置重写记录
        public async Task<(IEnumerable<MoveHouseSpecificationServiceConfigOverWriteEntity>, int)> GetListAsync(int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();


            string sql = string.Format(@"{0} {1} ; SELECT COUNT(1) FROM  movehousespecificationserviceconfigoverwrite  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize
                  );

            return await this.databaseContext.QueryMultipleAsync<MoveHouseSpecificationServiceConfigOverWriteEntity, int>(sql, parameters);
        }

        public async Task<int> InsertAllAsync(IEnumerable<MoveHouseSpecificationServiceConfigOverWriteEntity> entities)
        {
            return await this.databaseContext.Master.InsertAllAsync(entities, databaseContext.Transaction);
        }

        public async Task<MoveHouseSpecificationServiceConfigOverWriteEntity> GetEntityByConfigId(long moveHouseSpecificationServiceConfigId)
        {
            string sql = QUERY + " WHERE A.MoveHouseSpecificationServiceConfigId = ?MoveHouseSpecificationServiceConfigId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MoveHouseSpecificationServiceConfigId", moveHouseSpecificationServiceConfigId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<MoveHouseSpecificationServiceConfigOverWriteEntity>(sql, parameters);
        }

        public async Task<IEnumerable<MoveHouseSpecificationServiceConfigOverWriteEntity>> GetListByMoveHouseConfigId(long moveHouseConfigId, string provinceCode,
            string cityCode, string countyCode, long? agentRecordId, bool? enabled)
        {

            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Boolean);
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {QUERY} {whereSql} ";
            return await this.databaseContext.Slave.QueryAsync<MoveHouseSpecificationServiceConfigOverWriteEntity>(sql, parameters);
        }
        #endregion

        public async Task<IEnumerable<MoveHouseMinPriceEntity>> GetMinPrice(string provinceCode, string cityCode, string countyCode,
           long? agentRecordId, bool? isEnabled)
        {
            var sqlSelect = @" SELECT A.MoveHouseConfigId,MIN(A.Price) AS Price
                        FROM MoveHouseSpecificationServiceConfigOverWrite A  ";
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add(" A.ProvinceCode = ?ProvinceCode ");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add(" A.CityCode = ?CityCode ");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add(" A.CountyCode = ?CountyCode ");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            if (agentRecordId.HasValue)
            {
                wheres.Add(" A.AgentRecordId = ?AgentRecordId ");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (isEnabled.HasValue)
            {
                wheres.Add(" A.Enabled = ?Enabled ");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            string sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";
            var sql = $"{sqlSelect} {sqlWhere} GROUP BY A.MoveHouseConfigId ";
            return await this.databaseContext.Slave.QueryAsync<MoveHouseMinPriceEntity>(sql, parameters);
        }

        public async Task<MoveHouseSpecificationServiceConfigOverWriteEntity> GetEntityByConfigId(long moveHouseSpecificationServiceConfigId, long agentRecordId, string countyCode)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Boolean);
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            wheres.Add(" A.MoveHouseSpecificationServiceConfigId = ?MoveHouseSpecificationServiceConfigId ");
            parameters.Add("?MoveHouseSpecificationServiceConfigId", moveHouseSpecificationServiceConfigId, DbType.Int64);
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {QUERY} {whereSql} ";
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<MoveHouseSpecificationServiceConfigOverWriteEntity>(sql, parameters);
        }
    }
}



